﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient; 

public partial class DepartChange : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        TextBox4.Text = Request.QueryString["param2"];
        TextBox9.Text = Request.QueryString["param3"];
        if (Request.QueryString["param1"].Substring(0, 1).Equals("1"))
        {
            TextBox7.Text = "北京";
        }
        if (Request.QueryString["param1"].Substring(0, 1).Equals("2"))
        {
            TextBox7.Text = "上海";
        }
        if (Request.QueryString["param1"].Substring(0, 1).Equals("3"))
        {
            TextBox7.Text = "广州";
        }
        
        if (DropDownList2.Items.Count == 0)
        {
            OperateSQL osql = new OperateSQL();
            string sqlstring = "select DeptID,DeptName from dept ";
            DataSet list_ds = osql.GetBJDataSet(sqlstring);
            DropDownList2.DataSource = list_ds;
            DropDownList2.DataTextField = "DeptName";
            DropDownList2.DataValueField = "DeptId";
            DropDownList2.DataBind();
        }
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        OperateSQL osql = new OperateSQL();
        string sqlstring = "select DeptID,DeptName from dept ";
        DataSet list_ds = null;
         
        if(DropDownList1.SelectedValue.Equals("1"))
        {
            list_ds = osql.GetBJDataSet(sqlstring);
        }
        if (DropDownList1.SelectedValue.Equals("2"))
        {
            list_ds = osql.GetSHDataSet(sqlstring);
        }
        if (DropDownList1.SelectedValue.Equals("3"))
        {
            list_ds = osql.GetGZDataSet(sqlstring);
        }
        DropDownList2.DataSource = list_ds;
        DropDownList2.DataTextField = "DeptName";
        DropDownList2.DataValueField = "DeptId";
        DropDownList2.DataBind();
    }

    protected SqlConnection getConnction(string locId)
    {
        //北京
        if ("1".Equals(locId))
        {
            return new SqlConnection("Data Source=127.0.0.1;Initial Catalog=BJ;Persist Security Info=True;User ID=sa;Password=sa;");
        }
        //上海
        else if ("2".Equals(locId))
        {
            return new SqlConnection("Data Source=127.0.0.1;Initial Catalog=SH;Persist Security Info=True;User ID=sa;Password=sa;");
        }
        //广州
        else if ("3".Equals(locId))
        {
            return new SqlConnection("Data Source=127.0.0.1;Initial Catalog=GZ;Persist Security Info=True;User ID=sa;Password=sa;");
        }
        else
        {
            return null;
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        //获取旧员工ID对应的地点
        string oldLocId = Request.QueryString["param1"].Substring(0, 1);
        //获取新员工ID对应的地点
        string newLocId = TextBox10.Text.Substring(0, 1);

        //获取旧员工ID对应的地点的数据库连接
        SqlConnection fromConn = this.getConnction(oldLocId);
        //获取新员工ID对应的地点的数据库连接
        SqlConnection toConn = this.getConnction(newLocId);
        fromConn.Open();
       
        //删除旧员工信息
        SqlTransaction fromTrans = fromConn.BeginTransaction(IsolationLevel.ReadCommitted);
        SqlCommand fromCmd = new SqlCommand();
        fromCmd.CommandText = "select * from employee where EmployID = " + Request.QueryString["param1"];
        //fromCmd.Parameters.Add(new SqlParameter("@ID", oldLocId));
        fromCmd.CommandType = CommandType.Text;
        fromCmd.Connection = fromConn;
        fromCmd.Transaction = fromTrans; //启用事务

        SqlDataReader reader = fromCmd.ExecuteReader();
        //保存需要的值
        string employName;	//需要修改成实际需要的变量
        reader.Read();      
        string string_insert = "INSERT INTO employee (EmployID,EmployName,DeptID,ProjID,Email,Mobile,Sex,Status,OnboardDate,OffboardDate,Salary) VALUES (" +
            TextBox10.Text.ToString() + ",'" + reader.GetString(1) + "'," + DropDownList2.SelectedValue + "," +
            reader.GetValue(3).ToString() + ",'" + reader.GetValue(4).ToString() + "','" + reader.GetString(5) + "','" + reader.GetString(6) + "','" +
            reader.GetString(7) + "','" + reader.GetValue(8).ToString() + "','" + reader.GetValue(9).ToString() + "','" + reader.GetString(10) + "')";
        reader.Close();
        //删除员工
        fromCmd.CommandText = "delete from employee where EmployID = " + Request.QueryString["param1"];
        int effectedRows1 = fromCmd.ExecuteNonQuery();
        if (effectedRows1 != 1)
        {
            fromCmd.Transaction.Rollback();
            fromConn.Close();
            return;
        }

        //添加新员工
        toConn.Open();
        SqlTransaction toTrans = toConn.BeginTransaction(IsolationLevel.ReadCommitted);
        SqlCommand toCmd = new SqlCommand();
        //需要修改成实际需要的SQL
        toCmd.CommandText = string_insert;
        toCmd.CommandType = CommandType.Text;
        toCmd.Connection = toConn;
        toCmd.Transaction = toTrans; //启用嵌套事务
        int effectedRows2 = toCmd.ExecuteNonQuery();
        if (effectedRows2 != 1)
        {
            toCmd.Transaction.Rollback();
            toConn.Close();
            fromCmd.Transaction.Rollback();
            fromConn.Close();
        }

        fromCmd.Transaction.Commit();
        toCmd.Transaction.Commit();
       //String.Format("<script language={1}javascript{1}>alert({1}{0}{1});</script>","操作成功","/"");
        /*
        OperateSQL osql = new OperateSQL();
        DataSet table_ds = null;
        string sqlstring = "select * from employee where EmployID=" + Request.QueryString["param1"];
        
        string sqlstring_delete = "delete from employee where EmployID=" + Request.QueryString["param1"];
        if (TextBox7.Text.Equals("北京"))
        {  
            table_ds = osql.GetBJDataSet(sqlstring);
            osql.ExcuteBJSQLStatement(sqlstring_delete);
        }
        if (TextBox7.Text.Equals("上海"))
        {
            table_ds = osql.GetSHDataSet(sqlstring);
            osql.ExcuteSHSQLStatement(sqlstring_delete);
        }
        if (TextBox7.Text.Equals("广州"))
        {
            table_ds = osql.GetGZDataSet(sqlstring);
            osql.ExcuteGZSQLStatement(sqlstring_delete);
        }

        string sqlstring_insert = "INSERT INTO employee (EmployID,EmployName,DeptID,ProjID,Email,Mobile,Sex,Status,OnboardDate,OffboardDate,Salary) VALUES (" +
            TextBox10.Text.ToString() + ",'" + table_ds.Tables[0].Rows[0].ItemArray[1].ToString() + "'," + DropDownList2.SelectedValue + "," +
            table_ds.Tables[0].Rows[0].ItemArray[3].ToString() + ",'" + table_ds.Tables[0].Rows[0].ItemArray[4].ToString() + "','" +
            table_ds.Tables[0].Rows[0].ItemArray[5].ToString() + "','" + table_ds.Tables[0].Rows[0].ItemArray[6].ToString() + "','" +
            table_ds.Tables[0].Rows[0].ItemArray[7].ToString() + "','" + table_ds.Tables[0].Rows[0].ItemArray[8].ToString() + "','" +
            table_ds.Tables[0].Rows[0].ItemArray[9].ToString() + "','" + table_ds.Tables[0].Rows[0].ItemArray[10].ToString() + "')";
        if(DropDownList1.SelectedValue.Equals("1"))
        {
            osql.ExcuteBJSQLStatement(sqlstring_insert);
        }
        if (DropDownList1.SelectedValue.Equals("2"))
        {
            osql.ExcuteSHSQLStatement(sqlstring_insert);
        }
        if (DropDownList1.SelectedValue.Equals("3"))
        {
            osql.ExcuteGZSQLStatement(sqlstring_insert);
        }
        */
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string url = "DepartManager.aspx?";
        Response.Redirect(url);
    }
    
}
